Working with Dates and Times

Not only does Visual Basic let you store date and time information in the specific Date data type, it also provides a lot of date- and time-related functions. These functions are very important in all business applications and deserve an in-depth look.

Strictly speaking, Date and Time aren't functions: They're properties. In fact, you can use them to either retrieve the current date and time (as Date values) or assign new values to them to modify the system settings:

Print Date & " " & Time ' Displays "8/14/98 8:35:48 P.M.". ' Set a new system date using any valid date format. Date = "10/14/98" Date = "October 14, 1998"

The outdated Date$ and Time$ properties can also be used for the same task. They're String properties, however, and therefore recognize only the mm/dd/yy or mm/dd/yyyy formats and the hh:mm:ss and hh:mm formats, respectively. For this reason, it's usually better to use the new $-less functions.

The Now function returns a Date value that contains the current date and time:

Print Now ' Displays "8/14/98 8:35:48 P.M.".

But the time-honored Timer function returns the number of seconds elapsed from midnight and is more accurate than Now because the Timer function includes fractional parts of seconds. (The actual accuracy depends on the system.) This function is often used for benchmarking a portion of code:

StartTime = Timer ' Insert the code to be benchmarked here. Print Timer - StartTime

The preceding code suffers from some inaccuracy: The StartTime variable might be assigned when the system tick is about to expire, so your routine could appear to take longer than it actually does. Here's a slightly better approach:

StartTime = NextTimerTick ' Insert the code to be benchmarked here. Print Timer _ StartTime ' Wait for the current timer tick to elapse. Function NextTimerTick() As Single Dim t As Single t = Timer Do: Loop While t = Timer NextTimerTick = Timer End Function

If you're using the Timer function in production code, you should be aware that it's reset at midnight, so you always run the risk of introducing unlikely but potentially serious errors. Try to spot the bug in this routine, which adds a CPU-independent pause in your code:

' WARNING: this procedure has a bug. Sub BuggedPause(seconds As Integer) Dim start As Single start = Timer Do: Loop Until Timer _ start >= seconds End Sub

The bug manifests itself very rarely—for example, if the program asks for a 2-second pause at 11:59:59 p.m. Even if this probability is small, the effect of this minor bug is devastating and you'll have to press Ctrl+Alt+Del to kill your compiled application. Here's a way to work around this issue:

' The correct version of the procedure Sub Pause(seconds As Integer) Const SECS_INDAY = 24! * 60 * 60 ' Seconds per day Dim start As Single start = Timer Do: Loop Until (Timer + SECS_INDAY - start) Mod SECS_INDAY >= seconds End Sub

There are many ways to assemble a Date value. For example, you can use a Date constant, such as the following:

StartDate = #8/15/1998 9:20:57 PM#

but more often you'll build a Date value using one of the many functions that VBA gives you. The DateSerial function builds a Date value from its year/month/day components; similarly, the TimeSerial function builds a Time value from its hour/minute/second components:

Print DateSerial(1998, 8, 14) ' Displays "8/14/98" Print TimeSerial(12, 20, 30) ' Displays "12:20:30 P.M." ' Note that they don't raise errors with invalid arguments. Print DateSerial(1998, 4, 31) ' Displays "5/1/98"

The DateSerial function is also useful for determining indirectly whether a particular year is a leap year:

Function IsLeapYear(year As Integer) As Boolean ' Are February 29 and March 1 different dates? IsLeapYear = DateSerial(year, 2, 29) <> DateSerial(year, 3, 1) End Function

The DateValue and TimeValue functions return the date or time portions of their argument, which can be a string or a Date expression:

' The date a week from now Print DateValue(Now + 7) ' Displays "8/21/98"

A bunch of VBA functions let you extract date and time information from a Date expression or variable. The Day, Month, and Year functions return date values, whereas the Hour, Minute, and Second functions return time values:

' Get information about today's date. y = Year(Now): m = Month(Now): d = Day(Now) ' These functions also support any valid date format. Print Year("8/15/1998 9:10:26 PM") ' Displays "1998"

The Weekday function returns a number in the range 1 through 7, which corresponds to the day of the week of a given Date argument:

Print Weekday("8/14/98") ' Displays "6" (= vbFriday)

The Weekday function returns 1 when the date is the first day of the week. This function is locale aware, which means that under different localizations of Microsoft Windows it could consider the first day of the week to be different from vbSunday. In most cases, this condition doesn't affect the structure of your code. But if you want to be sure that 1 means Sunday, 2 means Monday, and so on, you can force the function to return a consistent value under all Windows systems, as follows:

Print Weekday(Now, vbSunday)

Although using the optional second argument forces the function to return the correct value, it doesn't change the system localization. If you next call the Weekday function without the second argument, it will still consider the first day of the week to be what it was before.

Finally you can extract any date and time information from a Date value or expression using the DatePart function, for which the syntax is

Result = DatePart(Interval, Date, [FirstDayOfWeek], [FirstWeekOfYear])

You'll rarely need to resort to this function because you can do most of your calculations using the other functions I've shown you so far. In two cases, however, this function is really useful:

' The quarter we are in Print DatePart("q", Now) ' Displays "3" ' The week number we are in (# of weeks since Jan 1st) Print DatePart("ww", Now) ' Displays "33"

The first argument can be one of the String constants listed in Table 5.1. For more information about the two optional arguments, see the description of the DateAdd function in the next section.

Table 5-1. Possible values for the interval argument in DatePart, DateAdd, and DateDiff functions.

Setting	Description
"yyyy"	Year
"q"	Quarter
"m"	Month
"y"	Day of the year (same as d)
"d"	Day
"w"	Weekday
"ww"	Week
"h"	Hour
"n"	Minute
"s"	Second

In most cases, you don't need any special functions to perform date arithmetic. All you need to know is that the integer part in a Date variable holds the date information, and the fractional part holds the time information:

' 2 days and 12 hours from now Print Now + 2 + #12:00# ' Displays "8/17/98 8:35:48 A.M."

For more sophisticated date math, you can use the DateAdd function, for which the syntax is the following:

NewDate = DateAdd(interval, number, date)

The interval is a string that indicates a date or time unit (see Table 5-1), number is the number of units you are adding, and date is the starting date. You can use this function to add and subtract date and time values:

' The date three months from now Print DateAdd("m", 3, Now) ' Displays "11/14/98 8:35:48 P.M." ' One year ago (automatically accounts for leap years) Print DateAdd("yyyy", -1, Now) ' Displays "8/14/97 8:35:48 P.M." ' The number of months since Jan 30, 1998 Print DateDiff("m", #1/30/1998#, Now) ' Displays "7" ' The number of days since Jan 30, 1998 _ you can use "d" or "y". Print DateDiff("y", #1/30/1998#, Now) ' Displays "196" ' The number of entire weeks since Jan 30, 1998 Print DateDiff("w", #1/30/1998#, Now) ' Displays "28" ' The number of weekends before 21st century - value <0 means ' future dates. ' Note: use "ww" to return the number of Sundays in the date interval. Print DateDiff("ww", #1/1/2000#, Now) ' Displays "-72"

When you have two dates and you want to evaluate the difference between them—that is, the time elapsed between one date and the next—you should use the DateDiff function, for which the syntax is

Result = DateDiff(interval, startdate, enddate _ [, FirstDayOfWeek[, FirstWeekOfYear]])

where interval has the meaning shown in Table 5-1, FirstDayOfWeek is an optional argument that you can use to specify which weekday should be considered as the first day of the week (you can use the constants vbSunday, vbMonday, and so on), and FirstWeekOfYear is another optional argument that lets you specify which week should be considered as the first week of the year. (See Table 5-2.)

Table 5-2. Possible values for the FirstWeekOfYear argument in the DateDiff function.

Constant	Value	Description
vbUseSystem	0	Use the NLS API setting.
vbFirstJan1	1	The first week is the one that includes January 1. (This is the default value for this setting.)
vbFirstFourDays	2	The first week is the first one that has at least four days in the new year.
vbFirstFullWeek	3	This first week is the first one that's completely contained in the new year.

The most important and flexible function for formatting date and time values is the Format function. This function gives you seven different, named formats for date and time:

• General Date (date and time in general format; only the date if the fractional part is 0; only the time if the integer part is 0)
• Long Date (for example, Friday, August 14, 1998, but results vary depending on your locale)
• Medium Date (for example, 14-Aug-98)
• Short Date (for example, 8/14/98)
• Long Time (for example, 8:35:48)
• Medium Time (for example, 8:35 A.M.)
• Short Time (for example, 8:35 in a 24 hour format)

You also have a few special characters with which you can build your own custom date and time format strings, including one- and two-digit day and month numbers, complete or abbreviated month and weekday names, a.m/p.m. indicators, week and quarter numbers, and so on:

' mmm/ddd = abbreviated month/weekday, ' mmmm/dddd = complete month/weekday Print Format(Now, "mmm dd, yyyy (dddd)") ' "Aug 14, 1998 (Friday)" ' hh/mm/ss always use two digits, h/m/s use one or two digits Print Format(Now, "hh:mm:ss") ' "20:35:48" Print Format(Now, "h:mm AMPM") ' "8:35 P.M." ' y=day in the year, ww=week in the year, q=quarter in the year ' Note how a backslash can be used to specify literal characters. Print Format(Now, "mm/dd/yy (\d\a\y=y \w\e\e\k=ww \q\u\a\r\t\e\r=q)") ' Displays "08/14/98 (day=226 week=33 quarter=3)"

Visual Basic 6 has introduced the new FormatDateTime function. It's far less flexible than the standard Format function and permits only a subset of the Format function's named formats. The only advantage of the FormatDateTime function is that it's also supported under VBScript and so can contribute to the ease of porting pieces of code from Visual Basic and VBA to VBScript and vice versa. Its syntax is

result = FormatDateTime(Expression, [NamedFormat])

where NamedFormat can be one of the following intrinsic constants: 0-vbGeneralDate (the default), 1-vbLongDate, 2-vbShortDate, 3-vbLongTime, or 4-vbShortTime. Here are a few examples:

Print FormatDateTime(Now) ' "8/14/98 8:35:48 P.M." Print FormatDateTime(Now, vbLongDate) ' "Saturday, August 15, 1998" Print FormatDateTime(Now, vbShortTime) ' "20:35"

Visual Basic 6 also includes two new functions related to date formatting. The MonthName function returns the complete or abbreviated name of a month, whereas the WeekdayName function returns the complete or abbreviated name of a weekday. Both are locale aware, so you can use them to list month and weekday names in the language the operating system has been configured for:

Print MonthName(2) ' "February" Print MonthName(2, True) ' "Feb" Print WeekdayName(1, True) ' "Sun"